package com.wnn.demo.model.hospital.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.wnn.demo.domain.Hospital;
import com.wnn.demo.model.hospital.mapper.HospitalMapper;
import com.wnn.demo.utils.IdWorker;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.*;


/**
 * @Auther: wn
 * @Date: 2019/11/6 15:27
 * @Description:
 */
@Service
public class HospitalService {

    @Autowired
    private HospitalMapper hospitalMapper;

    @Autowired
    private IdWorker idWorker;

    /**
     * 查找所有医院信息
     * @param page
     * @param limit
     * @return
     */
    public PageInfo< Hospital > selectHospitalAll(Integer page, Integer limit) {

        PageHelper.startPage(page,limit);
        List< Hospital > info= hospitalMapper.selectAll();
        return new PageInfo< >(info);
    }

    /**
     * 新增医院
     * @param hospital
     * @return
     */
    public int insertHospital(Hospital hospital) {
        String id = idWorker.nextId() + "";
        // 获取一个Date对象
        java.util.Date date = new java.util.Date();
        Timestamp timeStamp = new Timestamp(date.getTime());
        hospital.setId(id);
        hospital.setCreateTime(timeStamp);


        return  hospitalMapper.insert(hospital);
    }

    /**
     * 更新医院
     * @param hospital
     * @return
     */
    public int updateHospital(Hospital hospital) {

        Example example=new Example(Hospital.class);
        example.createCriteria().andEqualTo("id",hospital.getId());

        return hospitalMapper.updateByExampleSelective(hospital,example);
    }

    public int deleteHospitalById(String id) {

        return hospitalMapper.deleteByPrimaryKey(id);
    }

    public int batchHospitalByIds(String ids) {
        return hospitalMapper.deleteByIds(ids);
    }

    public List< Map > findHospAndDeptMenu() {
        //得到set省
        List< Hospital > hospitals = hospitalMapper.selectSetPro();
        ArrayList< Map > list = new ArrayList<>();
        //再在去重省里面封装child城市
        for (Hospital p:hospitals
        ) {
            HashMap< String, Object > proMap = new HashMap<>();
            proMap.put("Id",p.getId());
            proMap.put("title",p.getProvince());
            proMap.put("type","province");

            //当前省拥有的所有城市
            List< Hospital > nowProHaveCity = hospitalMapper.selectSetCityByPro(p.getProvince());
            ArrayList< Map > cList = new ArrayList<>();
            for (Hospital c:nowProHaveCity
            ) {
                HashMap< String, Object > cityMap = new HashMap<>();
                cityMap.put("Id",c.getId());
                cityMap.put("title",c.getCity() );
                cityMap.put("type","city");
                //当前城市拥有的所有区域
                List< Hospital > nowCityHaveArea = hospitalMapper.selectnowCityHaveAreaByCity(c.getCity());
                // 封装每个城市拥有的区域
                ArrayList< Map > aList = new ArrayList<>();
                for (Hospital a:nowCityHaveArea
                ) {
                    HashMap< String, Object > areaMap = new HashMap<>();
                    areaMap.put("Id",a.getId());
                    areaMap.put("title",a.getArea());
                    areaMap.put("type","area");
                    areaMap.put("isLeaf",true);
                    // 封装区域拥有的医院
                    List< Hospital > nowAreaHaveHosp = hospitalMapper.selectHospByArea(a.getArea());
                    ArrayList< Map > hList = new ArrayList<>();
                    for (Hospital h:nowAreaHaveHosp
                    ) {
                        HashMap< String, Object > hospMap = new HashMap<>();
                        //每个医院的ID
                        String hospitalId = h.getId();
                        //每个医院的名称
                        String hospitalName = h.getName();
                        hospMap.put("Id",hospitalId);
                        hospMap.put("title",hospitalName);

                        //用于树节点点击的查询条件
                        hospMap.put("hospitalId",hospitalId);
                        hospMap.put("type","hospital");
                        //得到当前医院的所有部门
                        List<Map<String,Object>> deptAll = hospitalMapper.selectHospHaveAllDept(h.getId(),0,"0");
                        ArrayList< Map > deptList = new ArrayList<>();
                        for (Map<String,Object> dl:deptAll
                        ) {
                            HashMap< String, Object > deptMap = new HashMap<>();
                            String hospId = (String) dl.get("hid");
                            //每个部门的Id
                            String deptId = (String) dl.get("departmentId");
                            //每个医院部门的Id
                            deptMap.put("Id",deptId);
                            deptMap.put("title",dl.get("departmentName"));

                            //用于树节点点击的查询条件
                            deptMap.put("hospitalId",hospitalId);
                            deptMap.put("deptId",deptId);
                            deptMap.put("type","dept");
                            //得到当前部门的子集
                            List<Map<String,Object>> deptChildAll  = hospitalMapper.selectHospHaveAllDept(hospId,1,deptId);
                            ArrayList< Map > deptChildList = new ArrayList<>();
                            for (Map<String,Object> dcl: deptChildAll
                            ) {
                                HashMap< String, Object > deptChildMap = new HashMap<>();

                                String childrenDeptId = (String) dcl.get("departmentId");
                                String  childrenDeptName = (String) dcl.get("departmentName");
                                deptChildMap.put("Id",childrenDeptId);
                                deptChildMap.put("title",childrenDeptName);

                                //用于树节点点击的查询条件
                                deptChildMap.put("hospitalId",hospitalId);
                                //部门ID
                                deptChildMap.put("deptId",deptId);
                                //科室ID
                                deptChildMap.put("deptChildId",childrenDeptId);
                                deptChildMap.put("type","childrenDept");
                                deptChildList.add(deptChildMap);
                            }

                            deptMap.put("children",deptChildList);
                            deptList.add(deptMap);
                        }
                        hospMap.put("children",deptList);
                        hList.add(hospMap);
                    }
                    areaMap.put("children",hList);
                    aList.add(areaMap);
                }
                //地区列表
                cityMap.put("children",aList);
                cList.add(cityMap);
            }
            //城市列表
            proMap.put("children",cList);
            list.add(proMap);
        }

        return list;
    }

    public void exportHosipitalExcle() {
        this.exportHosipitalExcle2();
    }


    /**
     * 普通列表导出
     */
    public void exportHosipitalExcle1() {
        String[] tableHeaders = {"等级","医院名称","地区","详细地址"};
        List< Hospital > info= hospitalMapper.selectAll();
        //处理好的数据
        ArrayList<List<String>> rowLists = new ArrayList<>();
        ArrayList<String> cellLists = new ArrayList<>();
        for (Hospital h:info
        ) {
            cellLists.add(h.getRank());
            cellLists.add(h.getName());
            cellLists.add(h.getCity());
            cellLists.add(h.getArea());
            rowLists.add(cellLists);
        }


        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本

        //创建头列 字体格式1
        CellStyle cellStyle = wb.createCellStyle();
        // 文字居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式位于单元格高度的中心
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = wb.createFont();
        //设置字体颜色
        font.setColor(HSSFColor.BLACK.index);
        //设置加粗
        font.setBold(true);
        cellStyle.setFont(font);

        //创建列 数据格式2
        CellStyle dataStyle = wb.createCellStyle();
        // 文字居中
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式位于单元格高度的中心
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);


        //创建表单sheet
        Sheet sheet = wb.createSheet("按箱导出光纤资源表");
        Row row = sheet.createRow(0);
        sheet.setColumnWidth(1, 27*256);

        for (int i = 0; i <tableHeaders.length ; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(tableHeaders[i]);
            cell.setCellStyle(cellStyle);
        }

        for (int rowIndex = 1; rowIndex < rowLists.size(); rowIndex++) {
            Row dataRow = sheet.createRow(rowIndex);
            List<String> cellList = rowLists.get(rowIndex);
            for (int cellIndex = 0; cellIndex < 4; cellIndex++) {
                Cell dataCell = dataRow.createCell(cellIndex);
                dataCell.setCellValue( cellList.get(cellIndex));
                dataCell.setCellStyle(dataStyle);
            }
        }

        //文件流
        try {
            FileOutputStream pis = new FileOutputStream("F:\\temp\\按箱导出3.xlsx");
            //写入文件
            wb.write(pis);
            pis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    public void exportHosipitalExcle2() {

        String[] fiberTableHeaders = {"业务方向","纤序","具体业务名称","转接方向"};
        String[] rowBusinessToPretend = {"业务方向","南宁方向","南宁方向","南宁方向","宾阳方向","南宁方向"
                                         ,"来宾方向","南宁方向","来宾方向","来宾方向","北京方向","天津方向","天津方向"};
        String[] rowFiberIndexPretend = {"纤序","1","2","3","4","5","6","7","8","9","10","11","12"};
        String[] rowBusinessNamePretend = {"具体业务名称","来宾2","来宾1","柳州站10","南宁方向","宾阳方向","柳州站6"
                                                 ,"来宾方向","南宁方向","柳州站7","南宁方向","柳州站9","柳州站10","宾阳方向"};
        String[] rowFiberToPretend = {"转接方向","ODF2-1-1-3","ODF2-2-1-4","ODF2-1-1-3","ODF2-2-2-6","ODF2-1-1-3","ODF2-2-3-1"
                                                ,"ODF2-2-3-1","ODF2-2-2-6","ODF2-2-1-4","ODF2-2-3-7","ODF2-2-3-1","ODF2-2-1-4","ODF2-2-3-7"};

        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建列格式
        CellStyle cellStyle = wb.createCellStyle();
        // 文字居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式位于单元格高度的中心
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        Font font = wb.createFont();
        //设置字体颜色
        font.setFontHeightInPoints((short) 12);//设置excel数据字体大小
        font.setFontName("宋体");
        font.setColor(HSSFColor.BLACK.index);
        //设置加粗
        font.setBold(true);
        cellStyle.setFont(font);
        //创建表单sheet
        Sheet sheet = wb.createSheet("按箱导出光纤资源表");
        //设置列宽
        for (int beginCellIndex= 0;beginCellIndex<15;beginCellIndex++) {
            sheet.setColumnWidth(beginCellIndex, 17*256);
        }
        ArrayList<String> sameList = new ArrayList<>();
        ArrayList<CellRangeAddress> sameMergeCellList = new ArrayList<>();
        int nowNumIndexRow =0;
        int nowNumIndexCell =0;
        //4个箱
        for (int boxCount = 0; boxCount < 6; boxCount++) {
            int allCount =  4;
            if (nowNumIndexRow!=0){
                //占位
                Row row2 = sheet.createRow(nowNumIndexRow+1);
                Cell cell2 = row2.createCell(0);
                cell2.setCellValue("");

                Row row3 = sheet.createRow(nowNumIndexRow+2);
                Cell cell3 = row3.createCell(0);
                cell3.setCellValue("");

                nowNumIndexRow = nowNumIndexRow+3;
            }
            for (int headIndexNum = 0; headIndexNum <  allCount+2; headIndexNum++) {
                if(headIndexNum!=0){
                    ++nowNumIndexRow;
                }
                //设置头  i当前行索引
                Row row = sheet.createRow(nowNumIndexRow);
                Cell cell = row.createCell(0);
                row.setHeightInPoints(20);
                cell.setCellValue("192.168.0.122-屏0001-箱");
                cell.setCellStyle(cellStyle);
                // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
                CellRangeAddress cra = new CellRangeAddress(   nowNumIndexRow,    nowNumIndexRow, 0, 14);
                sheet.addMergedRegion(cra);

                //为合并单元格添加边框
                RegionUtil.setBorderLeft(1,cra,sheet);
                RegionUtil.setBorderRight(1,cra,sheet);
                RegionUtil.setBorderBottom(1,cra,sheet);
                RegionUtil.setBorderTop(1,cra,sheet);

                //几个盘
                for (int diskCount = 0; diskCount <4 ; diskCount++) {
                    //每个盘设置4行
                    for (int j = 0; j < 4; j++) {
                        ++headIndexNum;
                        ++nowNumIndexRow;
                        // 第一行业务方向
                        if (j == 0) {
                            Row rowBusinessTo = sheet.createRow(nowNumIndexRow);
                            rowBusinessTo.setHeightInPoints(20);
                            for (int beginCellNum = 0; beginCellNum < rowBusinessToPretend.length; beginCellNum++) {
                                //取出被比较的数据
                                System.out.println(rowBusinessToPretend[beginCellNum] + ":"+ beginCellNum);

                                for (int nextCellNum = beginCellNum+1; beginCellNum < rowBusinessToPretend.length; beginCellNum++) {
//               第一次就相等就往下判断 ，诺第一次就不相等就没必要比对了 开启下一轮比对
                                    if (rowBusinessToPretend[beginCellNum].equals(rowBusinessToPretend[nextCellNum])){
                                        if (nextCellNum+1 <13){
                                            nextCellNum = this.check(rowBusinessToPretend, beginCellNum, nextCellNum+1 )-1;
                                        }
                                    }else {
//                    System.out.println("开始"+ beginCellNum + logSameCount + "结束");
                                        break;
                                    }
                                    if(beginCellNum!=nextCellNum){
                                        // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
                                        CellRangeAddress mergeCell = new CellRangeAddress(nowNumIndexRow, nowNumIndexRow, beginCellNum, nextCellNum);
                                        sameMergeCellList.add(mergeCell);
                                        sameList.add("数据:"+rowBusinessToPretend[beginCellNum] + "开始列"+ beginCellNum + "结束列"+nextCellNum);
                                        System.out.println("数据:"+rowBusinessToPretend[beginCellNum] + "开始列"+ beginCellNum + "结束列"+nextCellNum);
                                        beginCellNum =nextCellNum;
                                        break;
                                    }
                                }
                            }
                            for (int rowBusinessToIndex = 0; rowBusinessToIndex < 13; rowBusinessToIndex++) {
                                Cell rowBusiinesToCellData = rowBusinessTo.createCell(rowBusinessToIndex);
                                rowBusiinesToCellData.setCellValue(rowBusinessToPretend[rowBusinessToIndex]);
                                rowBusiinesToCellData.setCellStyle(cellStyle);

                            }
                            if (diskCount == 0){
                                //合并备注
                                Cell remarkCellData = rowBusinessTo.createCell(13);
                                remarkCellData.setCellValue("备注");
                                remarkCellData.setCellStyle(cellStyle);
                                // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
                                CellRangeAddress mergeRemark = new CellRangeAddress(   nowNumIndexRow, nowNumIndexRow+1, 13, 14);
                                //为合并单元格添加边框
                                RegionUtil.setBorderLeft(1,mergeRemark,sheet);
                                RegionUtil.setBorderRight(1,mergeRemark,sheet);
                                RegionUtil.setBorderBottom(1,mergeRemark,sheet);
                                RegionUtil.setBorderTop(1,mergeRemark,sheet);
                                sheet.addMergedRegion(mergeRemark);
                            }


                        }
                        // 第二行纤序
                        if (j == 1) {

                            Row rowFiber = sheet.createRow(nowNumIndexRow);
                            rowFiber.setHeightInPoints(20);
                            for (int rowFiberIndex = 0; rowFiberIndex < 13; rowFiberIndex++) {
                                Cell rowFiberIndexCell = rowFiber.createCell(rowFiberIndex);
                                int fiberNum = (diskCount *12) + rowFiberIndex  ;
                                if(rowFiberIndex==0){
                                    rowFiberIndexCell.setCellValue(  "纤序"  );
                                }else {
                                    rowFiberIndexCell.setCellValue(  fiberNum  );
                                }

                                rowFiberIndexCell.setCellStyle(cellStyle);
                            }
                        }
                        // 第三行具体业务名称
                        if (j == 2) {
                            Row rowBusinessName = sheet.createRow(nowNumIndexRow);
                            rowBusinessName.setHeightInPoints(20);
                            for (int rowBusinessNameIndex = 0; rowBusinessNameIndex < 13; rowBusinessNameIndex++) {
                                Cell rowBusinessNameCell = rowBusinessName.createCell(rowBusinessNameIndex);
                                rowBusinessNameCell.setCellValue(rowBusinessNamePretend[rowBusinessNameIndex]);
                                rowBusinessNameCell.setCellStyle(cellStyle);
                            }
                            if (diskCount == 0){
                                //合并备注
                                Cell portCountData = rowBusinessName.createCell(13);
                                portCountData.setCellValue("36");
                                portCountData.setCellStyle(cellStyle);
                                // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
                                CellRangeAddress mergePort = new CellRangeAddress(   nowNumIndexRow, nowNumIndexRow-3+4*4, 13, 13);
                                //为合并单元格添加边框
                                RegionUtil.setBorderLeft(1,mergePort,sheet);
                                RegionUtil.setBorderRight(1,mergePort,sheet);
                                RegionUtil.setBorderBottom(1,mergePort,sheet);
                                RegionUtil.setBorderTop(1,mergePort,sheet);
                                sheet.addMergedRegion(mergePort);
                            }
                            if (diskCount == 0){
                                //合并 箱号
                                Cell boxNameData = rowBusinessName.createCell(14);
                                boxNameData.setCellValue("箱1");
                                boxNameData.setCellStyle(cellStyle);
                                // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
                                CellRangeAddress mergePort = new CellRangeAddress(   nowNumIndexRow, nowNumIndexRow-3+4*4, 14, 14);
                                //为合并单元格添加边框
                                RegionUtil.setBorderLeft(1,mergePort,sheet);
                                RegionUtil.setBorderRight(1,mergePort,sheet);
                                RegionUtil.setBorderBottom(1,mergePort,sheet);
                                RegionUtil.setBorderTop(1,mergePort,sheet);
                                sheet.addMergedRegion(mergePort);
                            }
                        }
                        // 第四行转接方向
                        if (j == 3) {
                            Row rowFiberToRow = sheet.createRow(nowNumIndexRow);
                            rowFiberToRow.setHeightInPoints(20);
                            for (int rowFiberToRowIndex = 0; rowFiberToRowIndex < 13; rowFiberToRowIndex++) {
                                Cell rowFiberToCell = rowFiberToRow.createCell(rowFiberToRowIndex);
                                rowFiberToCell.setCellValue(rowFiberToPretend[rowFiberToRowIndex]);
                                rowFiberToCell.setCellStyle(cellStyle);
                            }
                        }
                    }


                    //合并端口数量

                    //合并箱号
                }

            }
        }

        for (CellRangeAddress cra:sameMergeCellList) {
            sheet.addMergedRegion(cra);
        }

        //文件流
        try {
            FileOutputStream pis = new FileOutputStream("F:\\temp\\按箱导出1.xlsx");
//            FileOutputStream pis = new FileOutputStream("F:\\temp\\原稿.xlsx");
            //写入文件
            wb.write(pis);
            pis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    private Integer check(String [] data,Integer beginCellNum,Integer nextCellNum ){
        if (data[beginCellNum].equals(data[nextCellNum])){
            return this.check(data, beginCellNum, nextCellNum+1 );
        }else {
            return nextCellNum;
        }
    }

    /**
     * 不合并
     */
    public void exportHosipitalExcle3() {

        String[] fiberTableHeaders = {"业务方向","纤序","具体业务名称","转接方向"};
        String[] rowBusinessToPretend = {"业务方向","南宁方向","南宁方向","南宁方向","宾阳方向","南宁方向"
                ,"来宾方向","南宁方向","来宾方向","来宾方向","来宾方向","南宁方向","宾阳方向"};
        String[] rowFiberIndexPretend = {"纤序","1","2","3","4","5","6","7","8","9","10","11","12"};
        String[] rowBusinessNamePretend = {"具体业务名称","ODF2-1-1-3","来宾1","柳州站10","南宁方向","宾阳方向","柳州站6"
                ,"来宾方向","南宁方向","柳州站7","南宁方向","柳州站9","柳州站10","宾阳方向"};
        String[] rowFiberToPretend = {"转接方向","ODF2-1-1-3","ODF2-2-1-4","ODF2-1-1-3","ODF2-2-2-6","ODF2-1-1-3","ODF2-2-3-1"
                ,"ODF2-2-3-1","ODF2-2-2-6","ODF2-2-1-4","ODF2-2-3-7","ODF2-2-3-1","ODF2-2-1-4","ODF2-2-3-7"};

        List<Hospital> hospitals = hospitalMapper.selectAll();
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建列格式
        CellStyle cellStyle = wb.createCellStyle();
        // 文字居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式位于单元格高度的中心
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        Font font = wb.createFont();
        //设置字体颜色
        font.setFontHeightInPoints((short) 12);//设置excel数据字体大小
        font.setFontName("宋体");
        font.setColor(HSSFColor.BLACK.index);
        //设置加粗
        font.setBold(true);
        cellStyle.setFont(font);
        //创建表单sheet
        Sheet sheet = wb.createSheet("按箱导出光纤资源表");
        //设置列宽
        for (int beginCellIndex= 0;beginCellIndex<14;beginCellIndex++) {
            sheet.setColumnWidth(beginCellIndex, 17*256);
        }
        for (int i = 0; i < hospitals.size() ; i++) {
            //设置头
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(i);
            row.setHeightInPoints(20);
            cell.setCellValue("192.168.0.122-屏0001-箱");
            cell.setCellStyle(cellStyle);
            // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 14);
//            CellRangeAddress craa = new CellRangeAddress(1, 1, 1, 3);
//            sheet.addMergedRegion(craa);
            sheet.addMergedRegion(cra);

            //为合并单元格添加边框
            RegionUtil.setBorderLeft(1,cra,sheet);
            RegionUtil.setBorderRight(1,cra,sheet);
            RegionUtil.setBorderBottom(1,cra,sheet);
            RegionUtil.setBorderTop(1,cra,sheet);
            //几个盘
            for (int diskCount = 0; diskCount <4 ; diskCount++) {
                //每个盘设置4行
                for (int j = 0; j < 4; j++) {
                    ++i;
                    // 第一行业务方向
                    if (j == 0) {
                        Row rowBusinessTo = sheet.createRow(i);
                        rowBusinessTo.setHeightInPoints(20);
                        for (int rowBusinessToIndex = 0; rowBusinessToIndex < 13; rowBusinessToIndex++) {
                            Cell rowBusiinesToCellData = rowBusinessTo.createCell(rowBusinessToIndex);
                            rowBusiinesToCellData.setCellValue(rowBusinessToPretend[rowBusinessToIndex]);
                            rowBusiinesToCellData.setCellStyle(cellStyle);

                        }
                    }
                    // 第二行纤序
                    if (j == 1) {
                        Row rowFiber = sheet.createRow(i);
                        rowFiber.setHeightInPoints(20);
                        for (int rowFiberIndex = 0; rowFiberIndex < 13; rowFiberIndex++) {
                            Cell rowFiberIndexCell = rowFiber.createCell(rowFiberIndex);
                            int fiberNum = (diskCount *12) + rowFiberIndex  ;
                            if(rowFiberIndex==0){
                                rowFiberIndexCell.setCellValue(  "纤序"  );
                            }else {
                                rowFiberIndexCell.setCellValue(  fiberNum  );
                            }

                            rowFiberIndexCell.setCellStyle(cellStyle);
                        }
                    }
                    // 第三行具体业务名称
                    if (j == 2) {
                        Row rowBusinessName = sheet.createRow(i);
                        rowBusinessName.setHeightInPoints(20);
                        for (int rowBusinessNameIndex = 0; rowBusinessNameIndex < 13; rowBusinessNameIndex++) {
                            Cell rowBusinessNameCell = rowBusinessName.createCell(rowBusinessNameIndex);
                            rowBusinessNameCell.setCellValue(rowBusinessNamePretend[rowBusinessNameIndex]);
                            rowBusinessNameCell.setCellStyle(cellStyle);
                        }
                    }
                    // 第四行转接方向
                    if (j == 3) {
                        Row rowFiberToRow = sheet.createRow(i);
                        rowFiberToRow.setHeightInPoints(20);
                        for (int rowFiberToRowIndex = 0; rowFiberToRowIndex < 13; rowFiberToRowIndex++) {
                            Cell rowFiberToCell = rowFiberToRow.createCell(rowFiberToRowIndex);
                            rowFiberToCell.setCellValue(rowFiberToPretend[rowFiberToRowIndex]);
                            rowFiberToCell.setCellStyle(cellStyle);
                        }
                    }

                }
            }

        }
        //把工号相同的单元格置空
        if(sheet.getLastRowNum()>2){//获取最后一行行标，比行数小  满足合并的条件，2是数据行的开始
            Row row_1 = sheet.getRow(2);
            Cell cell_0 = row_1.getCell(0);

            String staffcode = cell_0.getStringCellValue();

            for(int i=3;i<=sheet.getLastRowNum();i++){//除第一个外，循环将内容相同的单元格设为""，这里体现出为什么原数据要有序
                Row rows = sheet.getRow(i);
                //此处表示对4个单元格进行内容相同合并处理
                Cell cells_0 = rows.getCell(0);//工号
//				Cell cells_1 = rows.getCell(1);//姓名
//				Cell cells_2 = rows.getCell(5);//考试次数

                if(staffcode.equals(cells_0.getStringCellValue())){
                    cells_0.setCellValue("");
//					cells_1.setCellValue("");
//					cells_2.setCellValue(Cell.CELL_TYPE_BLANK);
                }else{
                    staffcode = cells_0.getStringCellValue();
                }

            }
        }
        //将为空的单元格与之前不为空的合并
        int sk;
        if(sheet.getLastRowNum()>2){
            sk=2;
            for(int i=3;i<=sheet.getLastRowNum();i++){
                Row rows = sheet.getRow(i);
                Cell cell_0 = rows.getCell(0);
                if(cell_0.getStringCellValue() == ""){
                    if(i==sheet.getLastRowNum()){
                        sheet.addMergedRegion(new CellRangeAddress(sk, i, 0, 0));
                        sheet.addMergedRegion(new CellRangeAddress(sk, i, 1, 1));
                        sheet.addMergedRegion(new CellRangeAddress(sk, i, 5, 5));
                    }
                }else{
                    if(sk != i-1){
                        sheet.addMergedRegion(new CellRangeAddress(sk, i-1, 0, 0));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(sk, i-1, 1, 1));
                        sheet.addMergedRegion(new CellRangeAddress(sk, i-1, 5, 5));
                    }
                    sk =i;
                }
            }
        }
        //文件流
        try {
            FileOutputStream pis = new FileOutputStream("F:\\temp\\按箱导出17.xlsx");
//            FileOutputStream pis = new FileOutputStream("F:\\temp\\原稿.xlsx");
            //写入文件
            wb.write(pis);
            pis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 存档
     */
    public void exportHosipitalExcle4() {

        String[] fiberTableHeaders = {"业务方向","纤序","具体业务名称","转接方向"};
        String[] rowBusinessToPretend = {"业务方向","南宁方向","南宁方向","南宁方向","宾阳方向","南宁方向"
                ,"来宾方向","南宁方向","来宾方向","来宾方向","北京方向","天津方向","天津方向"};
        String[] rowFiberIndexPretend = {"纤序","1","2","3","4","5","6","7","8","9","10","11","12"};
        String[] rowBusinessNamePretend = {"具体业务名称","ODF2-1-1-3","来宾1","柳州站10","南宁方向","宾阳方向","柳州站6"
                ,"来宾方向","南宁方向","柳州站7","南宁方向","柳州站9","柳州站10","宾阳方向"};
        String[] rowFiberToPretend = {"转接方向","ODF2-1-1-3","ODF2-2-1-4","ODF2-1-1-3","ODF2-2-2-6","ODF2-1-1-3","ODF2-2-3-1"
                ,"ODF2-2-3-1","ODF2-2-2-6","ODF2-2-1-4","ODF2-2-3-7","ODF2-2-3-1","ODF2-2-1-4","ODF2-2-3-7"};

        List<Hospital> hospitals = hospitalMapper.selectAll();
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建列格式
        CellStyle cellStyle = wb.createCellStyle();
        // 文字居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式位于单元格高度的中心
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        Font font = wb.createFont();
        //设置字体颜色
        font.setFontHeightInPoints((short) 12);//设置excel数据字体大小
        font.setFontName("宋体");
        font.setColor(HSSFColor.BLACK.index);
        //设置加粗
        font.setBold(true);
        cellStyle.setFont(font);
        //创建表单sheet
        Sheet sheet = wb.createSheet("按箱导出光纤资源表");
        //设置列宽
        for (int beginCellIndex= 0;beginCellIndex<15;beginCellIndex++) {
            sheet.setColumnWidth(beginCellIndex, 17*256);
        }
        ArrayList<String> sameList = new ArrayList<>();
        ArrayList<CellRangeAddress> sameMergeCellList = new ArrayList<>();
        int allCount = hospitals.size();
        for (int headIndexNum = 0; headIndexNum <  allCount+2; headIndexNum++) {
            //设置头  i当前行索引
            Row row = sheet.createRow(headIndexNum);
            Cell cell = row.createCell(headIndexNum);
            row.setHeightInPoints(20);
            cell.setCellValue("192.168.0.122-屏0001-箱");
            cell.setCellStyle(cellStyle);
            // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 14);
//            CellRangeAddress craa = new CellRangeAddress(1, 1, 1, 3);
//            sheet.addMergedRegion(craa);
            sheet.addMergedRegion(cra);

            //为合并单元格添加边框
            RegionUtil.setBorderLeft(1,cra,sheet);
            RegionUtil.setBorderRight(1,cra,sheet);
            RegionUtil.setBorderBottom(1,cra,sheet);
            RegionUtil.setBorderTop(1,cra,sheet);
            //几个盘
            for (int diskCount = 0; diskCount <4 ; diskCount++) {
                //每个盘设置4行
                for (int j = 0; j < 4; j++) {
                    ++headIndexNum;
                    // 第一行业务方向
                    if (j == 0) {
                        Row rowBusinessTo = sheet.createRow(headIndexNum);
                        rowBusinessTo.setHeightInPoints(20);

                        for (int beginCellNum = 0; beginCellNum < rowBusinessToPretend.length; beginCellNum++) {
                            if (beginCellNum==13){
                                break;
                            }
                            //取出被比较的数据
                            System.out.println(rowBusinessToPretend[beginCellNum] + ":"+ beginCellNum);

                            for (int nextCellNum = beginCellNum+1; beginCellNum < rowBusinessToPretend.length; beginCellNum++) {
//               第一次就相等就往下判断 ，诺第一次就不相等就没必要比对了 开启下一轮比对
                                if (rowBusinessToPretend[beginCellNum].equals(rowBusinessToPretend[nextCellNum])){
                                    if (nextCellNum+1 <13){
                                        nextCellNum = this.check(rowBusinessToPretend, beginCellNum, nextCellNum+1 )-1;
                                    }
                                }else {
//                    System.out.println("开始"+ beginCellNum + logSameCount + "结束");
                                    break;
                                }
                                if(beginCellNum!=nextCellNum){
                                    // 合并单元格：参数：起始行, 终止行, 起始列, 终止列
                                    CellRangeAddress mergeCell = new CellRangeAddress(headIndexNum, headIndexNum, beginCellNum, nextCellNum);
                                    sameMergeCellList.add(mergeCell);
                                    sameList.add("数据:"+rowBusinessToPretend[beginCellNum] + "开始列"+ beginCellNum + "结束列"+nextCellNum);
                                    System.out.println("数据:"+rowBusinessToPretend[beginCellNum] + "开始列"+ beginCellNum + "结束列"+nextCellNum);
                                    beginCellNum =nextCellNum;
                                    break;
                                }
                            }
                        }


                        for (int rowBusinessToIndex = 0; rowBusinessToIndex < 13; rowBusinessToIndex++) {
                            Cell rowBusiinesToCellData = rowBusinessTo.createCell(rowBusinessToIndex);
                            rowBusiinesToCellData.setCellValue(rowBusinessToPretend[rowBusinessToIndex]);
                            rowBusiinesToCellData.setCellStyle(cellStyle);

                        }
                    }
                    // 第二行纤序
                    if (j == 1) {
                        Row rowFiber = sheet.createRow(headIndexNum);
                        rowFiber.setHeightInPoints(20);
                        for (int rowFiberIndex = 0; rowFiberIndex < 13; rowFiberIndex++) {
                            Cell rowFiberIndexCell = rowFiber.createCell(rowFiberIndex);
                            int fiberNum = (diskCount *12) + rowFiberIndex  ;
                            if(rowFiberIndex==0){
                                rowFiberIndexCell.setCellValue(  "纤序"  );
                            }else {
                                rowFiberIndexCell.setCellValue(  fiberNum  );
                            }

                            rowFiberIndexCell.setCellStyle(cellStyle);
                        }
                    }
                    // 第三行具体业务名称
                    if (j == 2) {
                        Row rowBusinessName = sheet.createRow(headIndexNum);
                        rowBusinessName.setHeightInPoints(20);
                        for (int rowBusinessNameIndex = 0; rowBusinessNameIndex < 13; rowBusinessNameIndex++) {
                            Cell rowBusinessNameCell = rowBusinessName.createCell(rowBusinessNameIndex);
                            rowBusinessNameCell.setCellValue(rowBusinessNamePretend[rowBusinessNameIndex]);
                            rowBusinessNameCell.setCellStyle(cellStyle);
                        }
                    }
                    // 第四行转接方向
                    if (j == 3) {
                        Row rowFiberToRow = sheet.createRow(headIndexNum);
                        rowFiberToRow.setHeightInPoints(20);
                        for (int rowFiberToRowIndex = 0; rowFiberToRowIndex < 13; rowFiberToRowIndex++) {
                            Cell rowFiberToCell = rowFiberToRow.createCell(rowFiberToRowIndex);
                            rowFiberToCell.setCellValue(rowFiberToPretend[rowFiberToRowIndex]);
                            rowFiberToCell.setCellStyle(cellStyle);
                        }
                    }

                }
            }
        }
        for (CellRangeAddress cra:sameMergeCellList
        ) {
            sheet.addMergedRegion(cra);
        }


        //文件流
        try {
            FileOutputStream pis = new FileOutputStream("F:\\temp\\按箱导出1.xlsx");
//            FileOutputStream pis = new FileOutputStream("F:\\temp\\原稿.xlsx");
            //写入文件
            wb.write(pis);
            pis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
